﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace TruckWatch.Cloud.DAL
{
    public class DataGeneral
    {
        #region CreateConnection

        private static SqlConnection CreateConnection()
        {

            string cnName = "TruckWatch";
            //string sqlCnAzure = "Server=tcp:xlys58d5xv.database.windows.net,1433;Database=TruckWatch;User Id=applogin@xlys58d5xv;Password=Breathtaking4;Trusted_Connection=False;Encrypt=True";
            var cnInfo = ConfigurationManager.ConnectionStrings[cnName];
            if (cnInfo == null)
                throw new Exception("Connection string with name '" + cnName + "' not found");

            string cnStr = cnInfo.ConnectionString;

            SqlConnection cn = new SqlConnection(cnStr);

            cn.Open();
            return cn;
        }

        #endregion

        #region ExecuteReader

        public static SqlDataReader ExecuteReader(string procedureNameOrSql, List<SqlParameter> parameters, bool isStoredProcedure = false)
        {

            //IMPORTANT: make sure you wrap the returned SqlDataReader in a using statement so that it is closed. (You do not need to close the SqlConnection object.)
            SqlConnection cn = CreateConnection();

            SqlCommand cmd = new SqlCommand(procedureNameOrSql, cn);

            if (isStoredProcedure)
                cmd.CommandType = CommandType.StoredProcedure;
            if (parameters != null)
                cmd.Parameters.AddRange(parameters.ToArray());
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        #endregion

        #region DataTable

        public static DataTable GetDataTable(string procedureNameOrSql, List<SqlParameter> parameters, bool isStoredProcedure = false)
        {
            DataTable dt = new DataTable();
            using (SqlConnection cn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(procedureNameOrSql, cn);

                if (isStoredProcedure)
                    cmd.CommandType = CommandType.StoredProcedure;
                if (parameters != null)
                    cmd.Parameters.AddRange(parameters.ToArray());

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(dt);
            }
            return dt;
        }

        #endregion

        #region Scalar

        /// <summary>
        /// Wraps the SqlCommand.ExecuteScalar() method. 
        /// </summary>
        /// <param name="procedureNameOrSql">Stored procedure name or a Sql statement.</param>
        /// <param name="parameters">List of SqlParameter. Set to null if no parameters.</param>
        /// <param name="isStoredProcedure">True if the procedureNameOrSql is a stored procedure, false if it is a SQL statement.</param>
        /// <returns>Returns the first value of the first row of the Sql Statement.</returns>
        public static object GetScalar(string procedureNameOrSql, List<SqlParameter> parameters, bool isStoredProcedure = false)
        {
            object scalarValue;
            using (SqlConnection cn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(procedureNameOrSql, cn);
                if (isStoredProcedure)
                    cmd.CommandType = CommandType.StoredProcedure;
                if (parameters != null)
                    cmd.Parameters.AddRange(parameters.ToArray());
                scalarValue = cmd.ExecuteScalar();
            }
            return scalarValue;
        }

        #endregion

        #region ExecuteNonQuery

        /// <summary>
        /// Wraps the SqlCommand.ExecuteNonQuery() method. 
        /// </summary>
        /// <param name="procedureNameOrSql">Stored procedure name or a Sql statement.</param>
        /// <param name="parameters">List of SqlParameter. Set to null if no parameters.</param>
        /// <param name="isStoredProcedure">True if the procedureNameOrSql is a stored procedure, false if it is a SQL statement</param>
        /// <returns>Returns the number of rows affected by ExecuteNonQuery().</returns>
        public static int ExecuteNonQuery(string procedureNameOrSql, List<SqlParameter> parameters, bool isStoredProcedure = false)
        {
            int rowsAffected;
            using (SqlConnection cn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(procedureNameOrSql, cn);
                if (parameters != null)
                    cmd.Parameters.AddRange(parameters.ToArray());
                if (isStoredProcedure)
                    cmd.CommandType = CommandType.StoredProcedure;

                rowsAffected = cmd.ExecuteNonQuery();
            }
            return rowsAffected;
        }

        #endregion

        public static string GetDBServerName()
        {
            var cn = CreateConnection();
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(cn.ConnectionString);

            return builder.DataSource;
        }
    }

    #region ParamBuilder

    public class ParamBuilder
    {
        private readonly List<SqlParameter> _parameters = new List<SqlParameter>();
        public List<SqlParameter> Parameters
        {
            get
            {
                return _parameters;
            }
        }

        public void AddParam(SqlDbType sqlDbType, string paramName, object paramVal)
        {
            SqlParameter p = new SqlParameter(paramName, sqlDbType);
            p.Value = paramVal ?? DBNull.Value;
            _parameters.Add(p);
        }

        public SqlParameter AddOutputParam(SqlDbType sqlDbType, string paramName)
        {
            SqlParameter p = new SqlParameter(paramName, sqlDbType);
            p.Direction = ParameterDirection.Output;
            _parameters.Add(p);
            return p;
        }
    }

    #endregion
}
